﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using Extensions;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DAC
    {
        private string ConnectionString = "";
        public string ErrorMsg = "";
        public SqlConnection _dbConnection;
        public DataView dvData=new DataView();

        public DAC()
        {
            try
            {
                //ConnectionString="data source=.;initial catalog=mydashboard;user id=balaji2;password=test@123;";
                Class1 objc1 = new Class1();
                //objc1.methoda();

                

                if (ConfigurationManager.ConnectionStrings["dbconstring"].IsNotNullAndEmpty())                                
                {
                    ConnectionString = ConfigurationManager.ConnectionStrings["dbconstring"].ConnectionString;

                    _dbConnection = new SqlConnection(ConnectionString);
                    _dbConnection.Open();
                }
                else
                {
                    ErrorMsg = "Database connection string not found.";
                    Logger.Log(ErrorMsg);
                }
            }catch(Exception ex){
                ErrorMsg = ex.ToString();
                Logger.Log(ErrorMsg);
            }
        }

        public DAC(string constring)
        {
            try
            {
                if (constring.IsNotNullAndEmpty())
                {
                    _dbConnection = new SqlConnection(constring);
                    _dbConnection.Open();

                    //ConfigurationManager.ConnectionStrings.Remove("dbconstring");

                    //ConnectionStringSettings ocs = new ConnectionStringSettings();
                    //ocs.Name = "dbconstrin'g";
                    //ocs.ConnectionString = constring;
                    //ConfigurationManager.ConnectionStrings.Add(ocs);
                    //ConfigurationManager.RefreshSection("ConnectionStrings");
                }
                else
                {
                    ErrorMsg = "Database connection string not found.";
                    Logger.Log(ErrorMsg);
                }
            }
            catch (Exception ex)
            {
                ErrorMsg = ex.ToString();
                Logger.Log(ErrorMsg);
            }
        }

        public bool Save(string SQL) {
            bool status = true;
            
            try
            {
                SqlCommand cmdCommand = new SqlCommand(SQL, _dbConnection);
                cmdCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                status = false;
                ErrorMsg = ex.ToString();
                Logger.Log(ErrorMsg);
            }
            finally 
            {
                if (System.Data.ConnectionState.Open == _dbConnection.State)
                {
                    _dbConnection.Close();
                }
            }

            return status;
        }

        public bool Select(string SQL)
        {
            bool status = false;
            
            DataSet dsData = new DataSet();
            try
            {
                SqlCommand cmdCommand = new SqlCommand(SQL, _dbConnection);
                cmdCommand.ExecuteNonQuery();

                SqlDataAdapter sdaData = new SqlDataAdapter(cmdCommand);
                sdaData.Fill(dsData, "SUBROOT");
                dvData = dsData.Tables[0].DefaultView;
                status = true;
            }
            catch (Exception ex)
            {
                ErrorMsg = ex.ToString();
                Logger.Log(ErrorMsg);
            }
            finally
            {
                if (System.Data.ConnectionState.Open == _dbConnection.State)
                {
                    //_dbConnection.Close();
                }
            }

            return status;
        }
    }
}
